﻿using Microsoft.Data.SqlClient;

using System.Collections.Concurrent;

namespace LogNet6.SqlServer;
/// <summary>
/// SqlServer日志
/// </summary>
public class SqlServerLoggerProcessService : ISqlServerLoggerProcessor, ILoggerProcessor,IDisposable
{
    private readonly string _ConnectionString;
    private readonly string _ProjectName;

    private readonly BlockingCollection<LoggerPara> _MessageQueue = new();

    private readonly SqlConnection _CN;
    private readonly SqlCommand _Cmd;

    private readonly Thread _OutputThread;

    /// <summary>
    /// 
    /// </summary>
    /// <param name="connectionString">Sql Server连接字符串</param>
    /// <param name="projectName">项目名称</param>
    public SqlServerLoggerProcessService(string connectionString, string projectName)
    {
        _ConnectionString = connectionString;
        _ProjectName = projectName;
        _CN = new SqlConnection(connectionString);
        _Cmd = _CN.CreateCommand();
        _Cmd.CommandText = $"INSERT INTO Logs (ProjectName,Dt,Name,LogLevel,Msg) VALUES ('{projectName}',@Dt,@Name,@LogLevel,@Msg)";
        _Cmd.Parameters.Add(new SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 256));
        _Cmd.Parameters.Add(new SqlParameter("@LogLevel", System.Data.SqlDbType.NVarChar, 16));
        _Cmd.Parameters.Add(new SqlParameter("@Msg", System.Data.SqlDbType.NVarChar, int.MaxValue));
        _Cmd.Parameters.Add(new SqlParameter("@Dt", System.Data.SqlDbType.DateTime));

        // Start Console message queue processor
        _OutputThread = new Thread(ProcessLogQueue)
        {
            IsBackground = true,
            Name = "Logger queue processing thread"
        };
        _OutputThread.Start();
    }
    private void WriteMessage(LoggerPara p)
    {
        try
        {
            _Cmd.Parameters[0].Value = $"{p.CategoryName}";
            _Cmd.Parameters[1].Value = p.LogLevel.ToString();
            _Cmd.Parameters[2].Value = p.Message;
            _Cmd.Parameters[3].Value = p.DateTime;

            if (_CN.State == System.Data.ConnectionState.Closed)
                _CN.Open();
            _Cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
        }
    }
    private void ProcessLogQueue()
    {
        while (!_MessageQueue.IsCompleted)
        {
            try
            {
                WriteMessage(_MessageQueue.Take());
                if (_MessageQueue.Count == 0)
                    _CN.Close();
            }
            catch
            {
                break;
            }
        }
    }


    public void AddLog(LoggerPara message)
    {
        if (_MessageQueue.IsAddingCompleted)
            WriteMessage(message);
        else
            _MessageQueue.Add(message);

    }



    public void Dispose()
    {
        _MessageQueue.CompleteAdding();
        try
        {
            _OutputThread.Join(1500); // with timeout in-case Console is locked by user input
        }
        catch (ThreadStateException) { }

        _CN.Close();
    }

    public async Task<List<SqlserverLoggerItem>> GetLogsAsync(int pageNo, int pageSize)
    {
        using var cn = new SqlConnection(_ConnectionString);
        var cmd = cn.CreateCommand();
        cmd.CommandText = $@"WITH ta AS
(  
    SELECT Id,Name,LogLevel,Msg,Dt
    ROW_NUMBER() OVER (ORDER BY  id desc) AS RowNumber  
    FROM logs where ProjectName={_ProjectName}
)   
SELECT Id,Name,LogLevel,Msg,Dt
FROM ta
WHERE RowNumber BETWEEN {pageNo * pageSize + 1} AND {(pageNo + 1) * pageSize};  
";
        await cn.OpenAsync();
        var dr = await cmd.ExecuteReaderAsync();
        List<SqlserverLoggerItem> items = new();
        while (await dr.ReadAsync())
            items.Add(new SqlserverLoggerItem(dr.GetInt64(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4)));
        await dr.CloseAsync();
        return items;

    }

    public List<SqlserverLoggerItem> GetLogs(int pageNo, int pageSize)
    {
        using var cn = new SqlConnection(_ConnectionString);
        var cmd = cn.CreateCommand();
        cmd.CommandText = $@"WITH ta AS  
(  
    SELECT Id,Name,LogLevel,Msg,Dt,
    ROW_NUMBER() OVER (ORDER BY  id desc) AS RowNumber  
    FROM logs where ProjectName='{_ProjectName}'
)   
SELECT Id,Name,LogLevel,Msg,Dt
FROM ta
WHERE RowNumber BETWEEN {pageNo * pageSize + 1} AND {(pageNo + 1) * pageSize};  
";
        cn.Open();
        var dr = cmd.ExecuteReader();
        List<SqlserverLoggerItem> items = new();
        while (dr.Read())
            items.Add(new SqlserverLoggerItem(dr.GetInt64(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4)));
        dr.Close();
        return items;

    }

    public List<SqlserverLoggerItem> GetLogs(string searchText, int pageNo, int pageSize)
    {
        using var cn = new SqlConnection(_ConnectionString);
        var cmd = cn.CreateCommand();
        cmd.CommandText = $@"WITH ta AS  
(  
    SELECT Id,Name,LogLevel,Msg,Dt,
    ROW_NUMBER() OVER (ORDER BY  id desc) AS RowNumber  
    FROM logs where ProjectName='{_ProjectName}' and (Name like '%{searchText}%' or msg like '%{searchText}%')
)   
SELECT Id,Name,LogLevel,Msg,Dt
FROM ta
WHERE RowNumber BETWEEN {pageNo * pageSize + 1} AND {(pageNo + 1) * pageSize};  
";
        cn.Open();
        var dr = cmd.ExecuteReader();
        List<SqlserverLoggerItem> items = new();
        while (dr.Read())
            items.Add(new SqlserverLoggerItem(dr.GetInt64(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4)));
        dr.Close();
        return items;
    }
}